package unipi.helpers;

import java.awt.Color;
import java.io.File;
import java.io.IOException;
import java.util.List;

import javax.servlet.http.HttpServletRequest;

import org.jfree.chart.ChartFactory;
import org.jfree.chart.ChartUtilities;
import org.jfree.chart.JFreeChart;
import org.jfree.chart.plot.PlotOrientation;
import org.jfree.data.category.DefaultCategoryDataset;
import org.jfree.data.general.DatasetUtilities;
import org.jfree.data.general.DefaultPieDataset;
import org.jfree.data.xy.XYSeries;
import org.jfree.data.xy.XYSeriesCollection;

import unipi.database.SQLMethods;


public class StatChartHandler
{		
	private static void saveToPNG(String filename, JFreeChart chart, int width, int height) throws IOException
	{
		File file = new File(filename);			
		file.createNewFile();
		ChartUtilities.saveChartAsPNG(file, chart, width, height);
	}
	
	private static void saveToPNG(String filename, JFreeChart chart) throws IOException
	{
		saveToPNG(filename, chart, 450, 315);
	}
	
	private static JFreeChart createPieChart(String title, DefaultPieDataset dataset)
	{
		JFreeChart chart = ChartFactory.createPieChart(
				title,
				dataset, 
				true, //legend
				false, //tooltips
				false //urls
			); 
		
		chart.getPlot().setBackgroundPaint(Color.WHITE);
		chart.getPlot().setOutlinePaint(Color.WHITE);
		
		return chart;	
	}
	
	private static JFreeChart createBarChart(String title, String category, String value, DefaultCategoryDataset dataset)
	{
		JFreeChart chart = ChartFactory.createBarChart(
				title,         // chart title
                category,               // domain axis label
                value,                  // range axis label
                dataset,                  // data
                PlotOrientation.VERTICAL, // orientation
                false,                     // include legend
                true,                     // tooltips?
                false                     // URLs?
            );	       
				
		return chart;		
	}
	
	private static JFreeChart createLineChart(String title, String xname, String yname, XYSeriesCollection dataset)
	{
		JFreeChart chart = ChartFactory.createXYLineChart(
	            title,      // chart title
	            xname,                      // x axis label
	            yname,                      // y axis label
	            dataset,                  // data
	            PlotOrientation.VERTICAL,
	            true,                     // include legend
	            true,                     // tooltips
	            false                     // urls
	        );
		
		return chart;
	}
	
	
	// User Statistics
	
	public static String getLateCompletedPieChart(String uid, String hashedID, HttpServletRequest request) throws IOException
	{
		int ncols = 2;
		String query = "select count(t.id), (datediff(endDate,dueDate) > 0) as isLate from Tasks t\r\n" + 
						"inner join TaskAssignments ta \r\n" + 
						"on t.id = ta.taskID\r\n" + 
						"where ta.userID = " + uid + " and endDate is not null\r\n" + 
						"group by isLate";
		
		List<List<String>> result = SQLMethods.Select(query, ncols);
		
		if(result == null) return null;
		
		DefaultPieDataset dataset = new DefaultPieDataset();
		if(result.size() == 0)
			dataset.setValue("Ελλειπή δεδομένα", 1);
		else
		{		
			int notlate = 0;
			int late = 0;
			
			for(List<String> row : result)
			{
				if(row.get(1).equals("1"))
					late = Integer.parseInt(row.get(0));
				else
					notlate = Integer.parseInt(row.get(0));
			}
			
			double latePercent = (double)late / (late + notlate);
			double notlatePercent = 1 - latePercent;
			
			
			dataset.setValue("Καθυστέρησαν", latePercent);
			dataset.setValue("Στην ώρα τους", notlatePercent);
		}
		
		JFreeChart chart = createPieChart("Κατάσταση Ολοκληρωμένων", dataset);
		String fileName = "temp/relpie_" + hashedID + ".png";
		saveToPNG(request.getServletContext().getRealPath(fileName), chart);
		
		return fileName;
	}
	
	public static String getLatePendingPieChart(String uid, String hashedID, HttpServletRequest request) throws IOException
	{
		int ncols = 2;
		String query = "select count(t.id), (datediff(now(),dueDate) > 0) as isLate  from Tasks t\r\n" + 
						"inner join TaskAssignments ta \r\n" + 
						"on t.id = ta.taskID\r\n" + 
						"where ta.userID = " + uid + " and endDate is null\r\n" + 
						"group by isLate";
		
		List<List<String>> result = SQLMethods.Select(query, ncols);
		
		if(result == null) return null;
		DefaultPieDataset dataset = new DefaultPieDataset();
		if(result.size() == 0)
			dataset.setValue("Ελλειπή δεδομένα", 1);
		else
		{
			int late = 0;
			int notlate = 0;
			
			for(List<String> row : result)
			{
				if(row.get(1).equals("1"))
					late = Integer.parseInt(row.get(0));
				else
					notlate = Integer.parseInt(row.get(0));			
			}
			
			double latePercent = (double)late / (late + notlate);
			double notlatePercent = 1 - latePercent;
			
			
			dataset.setValue("Αργοπορημένες", latePercent);
			dataset.setValue("Μη αργοπορημένες", notlatePercent);
		}
		
		JFreeChart chart = createPieChart("Κατάσταση Τρέχουσων", dataset);
		String fileName = "temp/pcpie_" + hashedID + ".png";
		saveToPNG(request.getServletContext().getRealPath(fileName), chart);
		
		return fileName;		
	}

	public static String getProgressPerMonthBarChart(String uid, String hashedID, HttpServletRequest request) throws IOException
	{
		int ncols = 2;
		String query = "select count(id), month(dateWorked) from TaskProgress\r\n" + 
						"where userID = " + uid + "\r\n" + 
						"group by month(dateWorked)";
		
		List<List<String>> result = SQLMethods.Select(query, ncols);
		
		if(result == null) return null;
		//if(result.size() == 0) return null;
		
		int progressPerMonth[] = new int[12];
		
		for(List<String> row : result)
		{
			progressPerMonth[Integer.parseInt(row.get(1))-1] = Integer.parseInt(row.get(0));			
		}		
		
		DefaultCategoryDataset dataset = new DefaultCategoryDataset();
		
		for(int x = 0; x < 12; x++)
		{
			dataset.addValue(progressPerMonth[x], "", Integer.toString(x+1));
		}	
		
		JFreeChart chart = createBarChart("Πρόοδοι ανά μήνα", "Μήνες", "Πρόοδοι", dataset);		
		String fileName = "temp/ppmbar_" + hashedID + ".png";
		saveToPNG(request.getServletContext().getRealPath(fileName), chart);	
		
		return fileName;	
		
	}
	
	public static String getProgressPerMonthLineChart(String uid, String hashedID, HttpServletRequest request) throws IOException
	{
		int ncols = 2;
		String query = "select count(id), month(dateWorked) from TaskProgress\r\n" + 
						"where userID = " + uid + "\r\n" + 
						"group by month(dateWorked)";
		
		List<List<String>> prog = SQLMethods.Select(query, ncols);
		
		query = "select count(id), month(dateWorked) from TaskProgress\r\n" + 
				"group by month(dateWorked)";
		
		List<List<String>> allprog = SQLMethods.Select(query, ncols);
		
		if(prog == null || allprog == null) return null;
		//if(allprog.size() == 0) return null;
		
		int progressPerMonth[] = new int[12];
		int allProgressPerMonth[] = new int[12];
		
		for(List<String> row : prog)
		{
			progressPerMonth[Integer.parseInt(row.get(1))-1] = Integer.parseInt(row.get(0));			
		}	
		
		for(List<String> row : allprog)
		{
			allProgressPerMonth[Integer.parseInt(row.get(1))-1] = Integer.parseInt(row.get(0));			
		}
		
		XYSeriesCollection dataset = new XYSeriesCollection();
		XYSeries personal = new XYSeries("ατομικές");
		XYSeries total = new XYSeries("συνολικές");
		
		for(int x = 0; x < 12; x++)
		{
			personal.add(x+1, progressPerMonth[x]);
			total.add(x+1, allProgressPerMonth[x]);
		}	
		dataset.addSeries(personal);
		dataset.addSeries(total);
		
		JFreeChart chart = createLineChart("Πρόοδοι ανά μήνα", "Μήνες", "Πρόοδοι", dataset);		
		String fileName = "temp/ppmline_" + hashedID + ".png";
		saveToPNG(request.getServletContext().getRealPath(fileName), chart);	
		
		return fileName;	
		
	}
	
	public static String getProgressPerDayBarChart(String uid, String hashedID, HttpServletRequest request) throws IOException
	{
		int ncols = 2;
		String query = "select count(id), day(dateWorked) from TaskProgress\r\n" + 
						"where userID = " + uid + "\r\n" + 
						"and month(dateWorked) = month(now())\r\n" + 
						"group by day(dateWorked)";
		
		List<List<String>> result = SQLMethods.Select(query, ncols);
		
		if(result == null) return null;
		//if(result.size() == 0) return null;
		
		int progressPerDay[] = new int[31];
		
		for(List<String> row : result)
		{
			progressPerDay[Integer.parseInt(row.get(1))-1] = Integer.parseInt(row.get(0));			
		}		
		
		DefaultCategoryDataset dataset = new DefaultCategoryDataset();
		
		for(int x = 0; x < 31; x++)
		{
			dataset.addValue(progressPerDay[x], "", Integer.toString(x+1));
		}	
		
		JFreeChart chart = createBarChart("Πρόοδοι ανά ημέρα", "Μέρες", "Πρόοδοι", dataset);		
		String fileName = "temp/ppdbar_" + hashedID + ".png";
		saveToPNG(request.getServletContext().getRealPath(fileName), chart, 900, 315);	
		
		return fileName;	
		
	}

	public static String getTaskPerMonthBarChart(String uid, String hashedID, HttpServletRequest request) throws IOException
	{
		int ncols = 2;
		String query = "select count(title), month(startDate) as month from Tasks t\r\n" + 
						"inner join TaskAssignments ta\r\n" + 
						"on ta.taskid = t.id and ta.userid = " + uid + "\r\n" + 
						"group by month";
		
		List<List<String>> result = SQLMethods.Select(query, ncols);
		
		if(result == null) return null;
		//if(result.size() == 0) return null;
		
		int tasksPerMonth[] = new int[12];
		
		for(List<String> row : result)
		{
			tasksPerMonth[Integer.parseInt(row.get(1))-1] = Integer.parseInt(row.get(0));			
		}		
		
		DefaultCategoryDataset dataset = new DefaultCategoryDataset();
		
		for(int x = 0; x < 12; x++)
		{
			dataset.addValue(tasksPerMonth[x], "", Integer.toString(x+1));
		}	
		
		JFreeChart chart = createBarChart("Εργασίες ανά μήνα", "Μήνες", "Εργασίες", dataset);		
		String fileName = "temp/tpmbar_" + hashedID + ".png";
		saveToPNG(request.getServletContext().getRealPath(fileName), chart);	
		
		return fileName;	
	}
	
	
	//Department Statistics
	
	//Department Statistics
	
	public static String getDptProgressPerMonthLineChart(String dptID, String hashedID, HttpServletRequest request) throws IOException
	{
		int ncols = 2;
		String query = "select count(tp.id), month(dateWorked) from TaskProgress tp\r\n" + 
						"inner join Users u\r\n" + 
						"on tp.userID = u.id\r\n" + 
						"where u.dptID = " + dptID + "\r\n" + 
						"group by  month(dateWorked)";
		
		List<List<String>> prog = SQLMethods.Select(query, ncols);
		
		query = "select count(id), month(dateWorked) from TaskProgress\r\n" + 
				"group by month(dateWorked)";
		
		List<List<String>> allprog = SQLMethods.Select(query, ncols);
		
		if(prog == null || allprog == null) return null;
		//if(allprog.size() == 0) return null;
		
		int progressPerMonth[] = new int[12];
		int allProgressPerMonth[] = new int[12];
		
		for(List<String> row : prog)
		{
			progressPerMonth[Integer.parseInt(row.get(1))-1] = Integer.parseInt(row.get(0));			
		}	
		
		for(List<String> row : allprog)
		{
			allProgressPerMonth[Integer.parseInt(row.get(1))-1] = Integer.parseInt(row.get(0));			
		}
		
		XYSeriesCollection dataset = new XYSeriesCollection();
		XYSeries personal = new XYSeries("τμήματος");
		XYSeries total = new XYSeries("συνολικές");
		
		for(int x = 0; x < 12; x++)
		{
			personal.add(x+1, progressPerMonth[x]);
			total.add(x+1, allProgressPerMonth[x]);
		}	
		dataset.addSeries(personal);
		dataset.addSeries(total);
		
		JFreeChart chart = createLineChart("Πρόοδοι ανά μήνα", "Μήνες", "Πρόοδοι", dataset);		
		String fileName = "temp/ppmline_" + hashedID + ".png";
		saveToPNG(request.getServletContext().getRealPath(fileName), chart);	
		
		return fileName;	
		
	}
	
	public static String getDptProgressPerDayBarChart(String dptID, String hashedID, HttpServletRequest request) throws IOException
	{
		int ncols = 2;
		String query = "select count(tp.id), day(tp.dateWorked) from TaskProgress tp\r\n" + 
						"inner join Users u\r\n" + 
						"on tp.userID = u.id\r\n" + 
						"where month(dateWorked) = month(now()) and u.dptID = " + dptID + "\r\n" + 
						"group by day(dateWorked)";
		
		List<List<String>> result = SQLMethods.Select(query, ncols);
		
		if(result == null) return null;
		//if(result.size() == 0) return null;
		
		int progressPerDay[] = new int[31];
		
		for(List<String> row : result)
		{
			progressPerDay[Integer.parseInt(row.get(1))-1] = Integer.parseInt(row.get(0));			
		}		
		
		DefaultCategoryDataset dataset = new DefaultCategoryDataset();
		
		for(int x = 0; x < 31; x++)
		{
			dataset.addValue(progressPerDay[x], "", Integer.toString(x+1));
		}	
		
		JFreeChart chart = createBarChart("Πρόοδοι ανά ημέρα", "Μέρες", "Πρόοδοι", dataset);		
		String fileName = "temp/ppdbar_" + hashedID + ".png";
		saveToPNG(request.getServletContext().getRealPath(fileName), chart, 900, 315);	
		
		return fileName;	
		
	}

	public static String getDptLateCompletedPieChart(String dptID, String hashedID, HttpServletRequest request) throws IOException
	{
		int ncols = 2;
		String query = "select count(t.id), (datediff(endDate,dueDate) > 0) as isLate from Tasks t \r\n" + 
						"inner join TaskAssignments ta \r\n" + 
						"inner join Users u\r\n" + 
						"on t.id = ta.taskID and u.id = ta.userID\r\n" + 
						"where u.dptID = " + dptID + " and endDate is not null\r\n" + 
						"group by isLate";
		
		List<List<String>> result = SQLMethods.Select(query, ncols);
		
		if(result == null) return null;
		
		DefaultPieDataset dataset = new DefaultPieDataset();
		if(result.size() == 0)
			dataset.setValue("Ελλειπή Δεδομένα", 1);
		else
		{		
			int notlate = 0;
			int late = 0;
			
			for(List<String> row : result)
			{
				if(row.get(1).equals("1"))
					late = Integer.parseInt(row.get(0));
				else
					notlate = Integer.parseInt(row.get(0));
			}
			
			double latePercent = (double)late / (late + notlate);
			double notlatePercent = 1 - latePercent;
			
			
			dataset.setValue("Καθυστέρησαν", latePercent);
			dataset.setValue("Στην ώρα τους", notlatePercent);
		}
		
		JFreeChart chart = createPieChart("Κατάσταση Ολοκληρωμένων", dataset);
		String fileName = "temp/relpie_" + hashedID + ".png";
		saveToPNG(request.getServletContext().getRealPath(fileName), chart);
		
		return fileName;
	}

	public static String getDptLatePendingPieChart(String dptID, String hashedID, HttpServletRequest request) throws IOException
	{
		int ncols = 2;
		String query = "select count(t.id), (datediff(now(),dueDate) > 0) as isLate from Tasks t \r\n" + 
						"inner join TaskAssignments ta \r\n" + 
						"inner join Users u\r\n" + 
						"on t.id = ta.taskID and u.id = ta.userID\r\n" + 
						"where u.dptID = " + dptID + " and endDate is null\r\n" + 
						"group by isLate";
		
		List<List<String>> result = SQLMethods.Select(query, ncols);
		
		if(result == null) return null;
		
		DefaultPieDataset dataset = new DefaultPieDataset();
		if(result.size() == 0)
			dataset.setValue("Ελλειπή Δεδομένα", 1);
		else
		{		
			int late = 0;
			int notlate = 0;
			
			for(List<String> row : result)
			{
				if(row.get(1).equals("1"))
					late = Integer.parseInt(row.get(0));
				else
					notlate = Integer.parseInt(row.get(0));			
			}
			
			double latePercent = (double)late / (late + notlate);
			double notlatePercent = 1 - latePercent;
			
			
			dataset.setValue("Αργοπορημένες", latePercent);
			dataset.setValue("Μη αργοπορημένες", notlatePercent);
		}
		
		JFreeChart chart = createPieChart("Κατάσταση Τρέχουσων", dataset);
		String fileName = "temp/pcpie_" + hashedID + ".png";
		saveToPNG(request.getServletContext().getRealPath(fileName), chart);
		
		return fileName;		
	}

	public static String getDptTaskGroupsPieChart(String dptID, String hashedID, HttpServletRequest request) throws IOException
	{
		int ncols = 2;
		String query = "select count(t.id), coalesce(t.`group`, 'Χωρίς Ομάδα') from Tasks t\r\n" + 
						"inner join TaskAssignments ta\r\n" + 
						"inner join Users u\r\n" + 
						"on t.id = ta.taskID and ta.userID = u.id\r\n" + 
						"where u.dptID = " + dptID + "\r\n" + 
						"group by `group`";

		List<List<String>> result = SQLMethods.Select(query, ncols);
		
		if(result == null) return null;
		
		DefaultPieDataset dataset = new DefaultPieDataset();
		
		if(result.size() == 0)
			dataset.setValue("Ελλειπή Δεδομένα", 1);
		else
		{	
			int total = 0;
			double valuesum = 0;
			
			for(List<String> row : result)
			{
				total += Integer.parseInt(row.get(0));		
			}	
			
			for(int x = 0; x < result.size() - 1; x++)
			{
				double val = Integer.parseInt(result.get(x).get(0)) / (double)total;
				valuesum += val;
				dataset.setValue(result.get(x).get(1), val);
			}
			dataset.setValue(result.get(result.size() - 1).get(1),  1 - valuesum);
			
			dataset = (DefaultPieDataset) DatasetUtilities.createConsolidatedPieDataset(dataset, "Λοιπές Ομάδες", 0.05);
		}
		
		JFreeChart chart = createPieChart("Ομάδες Εργασιών", dataset);
		String fileName = "temp/grppie_" + hashedID + ".png";
		saveToPNG(request.getServletContext().getRealPath(fileName), chart);
		
		return fileName;		
		
	}

	public static String getDptTaskTeamsPieChart(String dptID, String hashedID, HttpServletRequest request) throws IOException
	{
		int ncols = 2;
		String query = "select count(teams.id), teams.isTeam from\r\n" + 
						"(\r\n" + 
						"    select t.id, count(u.id) > 1 as isTeam from Tasks t\r\n" + 
						"    inner join TaskAssignments ta\r\n" + 
						"    inner join Users u\r\n" + 
						"    on t.id = ta.taskID and ta.userID = u.id\r\n" + 
						"    where u.dptID = " + dptID + "\r\n" + 
						"    group by t.id\r\n" + 
						") teams\r\n" + 
						"group by teams.isTeam";
		
		List<List<String>> result = SQLMethods.Select(query, ncols);
		
		if(result == null) return null;
		
		DefaultPieDataset dataset = new DefaultPieDataset();
		if(result.size() == 0)
			dataset.setValue("Ελλειπή Δεδομένα", 1);
		else
		{		
			int isTeam = 0;
			int isSolo = 0;
			
			for(List<String> row : result)
			{
				if(row.get(1).equals("1"))
					isTeam = Integer.parseInt(row.get(0));
				else
					isSolo = Integer.parseInt(row.get(0));
			}
			
			double teamPercent = (double)isTeam / (isTeam + isSolo);
			double soloPercent = 1 - teamPercent;
			
			
			dataset.setValue("Ομαδικές", teamPercent);
			dataset.setValue("Ατομικές", soloPercent);
		}
		
		JFreeChart chart = createPieChart("Ομαδικές/Ατομικές Εργασίες", dataset);
		String fileName = "temp/teampie_" + hashedID + ".png";
		saveToPNG(request.getServletContext().getRealPath(fileName), chart);
		
		return fileName;

	}
}
